/**
 * 描述: 业务逻辑处理 - 单词接口
 * 作者: wz
 * 日期: 2023-12-25
*/


const { querySql, formatTime, innerUploadFile } = require('../utils/index');
const boom = require('boom');
const { body, validationResult } = require('express-validator');
const { 
  CODE_ERROR,
  CODE_SUCCESS, 
} = require('../utils/constant');

const fs = require('fs')
const xlsx = require('node-xlsx');      // 读写xlsx的插件

// 查询所有单词包列表
async function getBookList(req, res, next) {
  const err = validationResult(req);
  // 如果验证错误，empty不为空
  if (!err.isEmpty()) {
    // 获取错误信息
    const [{ msg }] = err.errors;
    // 抛出错误，交给我们自定义的统一异常处理程序进行错误返回 
    next(boom.badRequest(msg));
  } else {

    let { pageSize, pageNo, params } = req.body;
    
    // 默认值
    pageSize = pageSize ? pageSize : 1;
    pageNo = pageNo ? pageNo : 1;

    let query = `select id from sys_book d`;
    let paramsStr = ' where delete_flag=0';
    if( params){
      if( params.gmt_create){
        if( params.gmt_create.length > 0 && params.gmt_create[0]){
          paramsStr += ' and d.gmt_create >=\'' + params.gmt_create[0] + '\'';
          if( params.gmt_create.length > 1&& params.gmt_create[1]){
            paramsStr += ' and d.gmt_create <=\'' + params.gmt_create[1] + '\'';
          }
        }
      }
      if( params.name){
        paramsStr += ' and name like \'%' + params.name + '%\'';
      }
    }

    try{
      let data = await querySql(query + paramsStr)
    	// console.log('任务列表查询===', data);
      if (!data || data.length === 0) {
        res.json({ 
        	code: CODE_SUCCESS, 
        	msg: '暂无数据', 
        	data: null 
        })
      } else {
        // 计算数据总条数
        let total = data.length; 
        // 分页条件 (跳过多少条)
        let n = (pageNo - 1) * pageSize;
        // 拼接分页的sql语句命令
        let query_3 = `select d.id,name,book_desc,image,d.gmt_create,d.gmt_modify from sys_book d`;
        query_3 += paramsStr;
        query_3 += ` order by d.gmt_create desc limit ${n} , ${pageSize}`;
        let result_3 = await querySql(query_3);
        if (!result_3 || result_3.length === 0) {
          res.json({ 
            code: CODE_SUCCESS, 
            msg: '暂无数据', 
            data: null 
          })
        } else {
          res.json({ 
            code: CODE_SUCCESS, 
            msg: '查询数据成功', 
            data: {
              rows: result_3,
              total: total,
              pageNo: parseInt(pageNo),
              pageSize: parseInt(pageSize),
            } 
          })
        }
      }
    }catch( err){
      res.json({ 
        code: CODE_ERROR, 
        msg: '暂无数据', 
        data: err 
      })
    }
  }
}

// 添加词汇包
async function addBook(req, res, next) {
  const err = validationResult(req);
  if (!err.isEmpty()) {
    const [{ msg }] = err.errors;
    next(boom.badRequest(msg));
  } else {
    let { name, image, gmt_create} = req.body;
    if( !gmt_create || gmt_create.length === 0){
      gmt_create = formatTime(new Date());
    }
    if( !image){
      image = '';
    }

    const query = `insert into sys_book(name, image, gmt_create) values('${name}', '${image}', '${gmt_create}')`;
    console.log(query)
    try{  
      let data = await querySql(query);
      // console.log('添加任务===', data);
      if (!data || data.affectedRows === 0) {
        res.json({ 
          code: CODE_ERROR, 
          msg: '添加数据失败', 
          data: null 
        })
      } else {
        // console.log('更新用户余额===', data1);
        res.json({ 
          code: CODE_SUCCESS, 
          msg: '添加数据成功', 
          data: null 
        })
      }
    }catch( err){
      res.json({ 
        code: CODE_ERROR, 
        msg: '添加数据失败', 
        data: err 
      })
    }
  }
}

// 编辑词汇包
async function editBook(req, res, next) {
  const err = validationResult(req);
  if (!err.isEmpty()) {
    const [{ msg }] = err.errors;
    next(boom.badRequest(msg));
  } else {
    let { id, name, image, gmt_create} = req.body;
    let query = `update sys_book set name='${name}'`;
    if( image){
      query += `, image='${image}'`
    }
    if( gmt_create){
      query += `, gmt_create='${gmt_create}'`
    }
    query += ` where id='${id}'`;
    
    try{
      let data = await querySql(query);
      // console.log('编辑设备===', data);
      if (!data || data.length === 0) {
        res.json({ 
          code: CODE_ERROR, 
          msg: '更新数据失败', 
          data: null 
        })
      } else {
        res.json({ 
          code: CODE_SUCCESS, 
          msg: '更新数据成功', 
          data: null 
        })
      }
    }catch( err){
      res.json({ 
        code: CODE_ERROR, 
        msg: '更新数据失败', 
        data: err 
      })
    }
  }
}

// 删除词汇包
async function deleteBook(req, res, next) {
  const err = validationResult(req);
  if (!err.isEmpty()) {
    const [{ msg }] = err.errors;
    next(boom.badRequest(msg));
  } else {
    let { id } = req.body;

    const query = `update sys_book set delete_flag=1 where id='${id}'`;
    try{
      let data = await querySql(query)
      // console.log('删除设备===', data);
      if (!data || data.length === 0) {
        res.json({ 
          code: CODE_ERROR, 
          msg: '删除数据失败', 
          data: null 
        })
      } else {
        res.json({ 
          code: CODE_SUCCESS, 
          msg: '删除数据成功', 
          data: null 
        })
      }
    } catch( err) {
      res.json({ 
        code: CODE_ERROR, 
        msg: '删除数据失败', 
        data: err 
      })
    }
  }
}

function isChinese(str) {
  var reg = /^[\u4E00-\u9FA5\uF900-\uFA2D]+$/;
  return reg.test(str);
}

async function parseExcelFile(filename) {
  let list = xlsx.parse(filename);
  if( list.length > 0){
    
    let data = list[0].data;
    let temp = [];
    if( data.length > 1){
      for( let i = 1; i < data.length; i++){
        if(data[i].length <= 4 || !data[i][0]){
          continue;
        }
        let book_name = data[i][0] + data[i][1];
        book_name = book_name.replace(/['"\/\|\*?]/g, '’');
        let bookItem = temp.find( item=>{return item.book_name === book_name});
        if(!bookItem){
          bookItem = {book_name, units:[]};
          temp.push(bookItem);
        }
        let unit_name = data[i][2];
        unit_name = unit_name.replace(/['"\/\|\*?]/g, '’');
        let unitItem = bookItem.units.find( item=>{return item.unit_name === unit_name});
        if( !unitItem){
          unitItem = {unit_name, words:[]};
          bookItem.units.push(unitItem);
        }
        let words = [];
        // console.log(data[i][3], typeof data[i][3])
        if( data[i][3] && (typeof data[i][3] == 'string') && !isChinese(data[i][3])){
          let word = data[i][3];
          // 去掉特殊字符  
          word = word.replace(/['"\/\|\*?]/g, '’');
                 
          words.push(word);
          if( data[i][4]){
            words.push(data[i][4].replace(/['"]/g, '’'));
          }else{
            words.push('')
          }
          if( data[i][5]){
            words.push(data[i][5].replace(/['"]/g, '’'));
          }else{
            words.push('')
          }
          unitItem.words.push(words)
        }
      }
    }
    console.log('读取词汇包数据===', temp.length);
    // 查询所有单词
    let allWords = await querySql(`select book_id,word,word_zh,phonetic from sys_allword`);
    let allBooks = await querySql(`select id,name,delete_flag from sys_book`);
    let allUnits = await querySql(`select id,name,book_id,words,delete_flag from sys_unit`);
    for( let bookItem of temp){
      let book_name = bookItem.book_name;
      let data = allBooks.find( item=>{return item.name == book_name});
      console.log('查询词汇包数据===', data);
      let book_id = 0;
      if(!data){
        // 插入数据
        let insertQuery = `insert into sys_book(name) values ('${book_name}')`
        let insertData = await querySql(insertQuery);
        if( insertData && insertData.insertId > 0){
          book_id = insertData.insertId;
          allBooks.push({id: book_id, name:book_name});
        }
      }else{
        if( data.delete_flag == 1){
          // 已删除的数据恢复
          let deleteQuery = `update sys_book set delete_flag=0 where id=${data.id}`;
          await querySql(deleteQuery);
          data.delete_flag = 0;
        }
        book_id = data.id;
      }
      if( book_id > 0){
        for( let unitItem of bookItem.units){
          let unit_name = unitItem.unit_name;
          if( unitItem.words.length <= 0){
            continue;
          }
          let index = allUnits.findIndex( item=>{return item.name == unit_name && item.book_id === book_id});
          let strWords = '';
          for(let wordItem of unitItem.words){
            strWords += `${wordItem[0]},`;
          }
          strWords = strWords.substring(0, strWords.length - 1);
          console.log('查询单元数据===', data);
          if(index < 0){
            // 插入数据
            let insertQuery = `insert into sys_unit(name, book_id, words) values ('${unit_name}', ${book_id}, '${strWords}')`
            let insertData = await querySql(insertQuery);
            if( insertData && insertData.insertId > 0){
              allUnits.push({id: insertData.insertId, name:unit_name, book_id, words:strWords, is_find:true});
            }
          }else{
            data = allUnits[index];
            if( strWords !== data.words){
              // 更新数据
              let insertQuery = `update sys_unit set delete_flag=0,words='${strWords}' where id=${data.id}`;
              await querySql(insertQuery);
            }else if( data.delete_flag == 1){
              // 已删除的数据恢复
              let deleteQuery = `update sys_unit set delete_flag=0 where id=${data.id}`;
              await querySql(deleteQuery);
            }
            data.is_find = true;
          }
          
          let insertStr = [];
          for(let wordItem of unitItem.words){
            let bInsert = true;
            let arrFind = allWords.filter( item=> item.word == wordItem[0]);
            if( arrFind.length > 0){
              let firstItem = arrFind[0];
              if( arrFind.find(item=>item.book_id == book_id)){
                bInsert = false;
              }else{
                if( wordItem[1].length <= 0){
                  wordItem[1] = firstItem.word_zh;
                }
                if( wordItem[2].length <= 0){
                  wordItem[2] = firstItem.phonetic;
                }
              }
            }
            if( bInsert){
              let word = wordItem[0];
              let word_zh = wordItem[1];
              let phonetic = wordItem[2];
              insertStr.push( `('${word}','${word_zh}','${phonetic}','${book_id}')`);
              allWords.push({word, word_zh, phonetic, book_id});
            }
          }
          // 插入单词数据
          if( insertStr.length > 0){
            let insertQuery = `insert into sys_allword( word, word_zh, phonetic, book_id) values ${insertStr.join(',')}`
            await querySql(insertQuery);
          }
        }
        // 删除已删除的数据
        let deleteId = [];
        for( let item of allUnits){
          if( item.book_id == book_id && !item.is_find){
            deleteId.push(item.id);
          }
        }
        if( deleteId.length > 0){
          let deleteQuery = `update sys_unit set delete_flag=1 where id in (${deleteId.join(',')})`
          await querySql(deleteQuery);
        }
      }
    }
  }
}

// 导入词汇包
async function importBook(req, res, next) {
  const err = validationResult(req);
  if (!err.isEmpty()) {
    const [{ msg }] = err.errors;
    next(boom.badRequest(msg));
  } else {
    // 解析请求
    try{
      let {filepath, newFilename} = await innerUploadFile(req);
      console.log('文件', filepath);
      await parseExcelFile(filepath);
      fs.rmSync(filepath);
      res.json({ 
        code: CODE_SUCCESS, 
        msg: '上传数据成功', 
        data: {newFilename} 
      })
    }catch(err){
      console.log('err', err);
      res.json({ 
        code: CODE_ERROR, 
        msg: '上传数据失败', 
        data: err
      })
    }
  }
}

// 导入词汇包
async function uploadFile(req, res, next) {
  const err = validationResult(req);
  if (!err.isEmpty()) {
    const [{ msg }] = err.errors;
    next(boom.badRequest(msg));
  } else {
    // 解析请求
    try{
      let {filepath, newFilename} = await innerUploadFile(req);
      console.log('文件', filepath);
      res.json({ 
        code: CODE_SUCCESS, 
        msg: '上传数据成功', 
        data: {newFilename} 
      })
    }catch(err){
      console.log('err', err);
      res.json({ 
        code: CODE_ERROR, 
        msg: '上传数据失败', 
        data: err
      })
    }
  }
}

// 查询用户单词包列表
async function getUserBookList(req, res, next) {
  const err = validationResult(req);
  // 如果验证错误，empty不为空
  if (!err.isEmpty()) {
    // 获取错误信息
    const [{ msg }] = err.errors;
    // 抛出错误，交给我们自定义的统一异常处理程序进行错误返回 
    next(boom.badRequest(msg));
  } else {

    let { user_id } = req.body;

    try{
      let query = `select id,name,image from sys_book where delete_flag=0 and id in (select book_id from sys_user_book where user_id=${user_id})`;
      let data = await querySql(query)
    	// console.log('任务列表查询===', data);
      if (!data || data.length === 0) {
        res.json({ 
        	code: CODE_SUCCESS, 
        	msg: '暂无数据', 
        	data: null 
        })
      } else {
        let dataStudy = await querySql(`select book_id,words,words_known from sys_studyword where user_id=${user_id}`);
        let dataUnit = await querySql(`select book_id,words from sys_unit where delete_flag=0`);
        for(let item of data){        
          let allWords = new Set();
          for( let unit of dataUnit){
            // 计算总单词数
            if( unit.book_id == item.id){
              let words = unit.words.split(',');
              for(let word of words){  
                if(word.length > 0){    
                  allWords.add(word);
                }
              }
            }
          }
          item.wordsCount = allWords.size;
          // 计算已学习单词
          let studyWords = new Set();
          for( let study of dataStudy){
            if( study.book_id == item.id){      
              let words = study.words.split(',');
              for(let word of words){  
                if(word.length > 0){    
                  studyWords.add(word);
                }
              }
              if( study.words_known){
                let words = study.words_known.split(',');
                for(let word of words){      
                  if(word.length > 0){    
                    studyWords.add(word);
                  }
                }
              }
            }
          }
          item.studyCount = studyWords.size;
          if( item.studyCount > item.wordsCount){
            // 不能超过总单词数
            item.studyCount = item.wordsCount;
          }
        }
        res.json({ 
          code: CODE_SUCCESS, 
          msg: '查询数据成功', 
          data: {
            rows: data,
          } 
        })
      }
    }catch( err){
      console.log( err)
      res.json({ 
        code: CODE_ERROR, 
        msg: '暂无数据', 
        data: err 
      })
    }
  }
}

// 查询用户单词包id
async function getUserBookIds(req, res, next) {
  const err = validationResult(req);
  // 如果验证错误，empty不为空
  if (!err.isEmpty()) {
    // 获取错误信息
    const [{ msg }] = err.errors;
    // 抛出错误，交给我们自定义的统一异常处理程序进行错误返回 
    next(boom.badRequest(msg));
  } else {

    let { user_id } = req.body;
    if(!user_id){
      user_id = 0;
    }

    try{
      let query = `select book_id from sys_user_book where user_id=${user_id}`;
      let data = await querySql(query)
    	// console.log('任务列表查询===', data);
      if (!data || data.length === 0) {
        res.json({ 
        	code: CODE_SUCCESS, 
        	msg: '暂无数据', 
        	data: null 
        })
      } else {
        res.json({ 
          code: CODE_SUCCESS, 
          msg: '查询数据成功', 
          data: {
            rows: data
          } 
        })
      }
    }catch( err){
      res.json({ 
        code: CODE_ERROR, 
        msg: '暂无数据', 
        data: err 
      })
    }
  }
}

module.exports = {
  getBookList,
  addBook,
  editBook,
  deleteBook,
  importBook,
  uploadFile,
  getUserBookList,
  getUserBookIds
}
